# --*--coding: utf-8 --*--
__project__ = 'TestHomeWork'
__fileName = 'test17'
__author = 'admin'
__time = '2021/4/26 17:25'
__product__ = 'PyCharm'

import pymysql


# 登录
def main():
    try:
        while True:
            print("欢迎来到学生界面")
            nubmer = (input('你好,登录系统请回复:1 ,注册系统请回复:2, 退出请输入3\n'))
            if nubmer == 1:
                print('--------开始登录----------')
                studengt_login()
            elif nubmer == 2:
                print('--------学生信息注册--')
                insert_studends()
            elif nubmer == 3:
                print('系统推出')
                break
            else:
                print('输入有误，请重现输入')
    except Exception as error:
        print('系统错误%s' % error)


def studengt_login():
    try:
        # 连接数据库
        conn = pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
        # 打开游标
        # pymysql.cursors.DictCursor
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        name = str(input('请输入登录名'))
        pasword = eval(input('请输入学号'))

        sql = "select * from students17 where   name=%s  and studentNo=%s"
        conunt = cursor.execute(sql, [name, pasword])
        print(conunt)
        if conunt >= 1:
            print('登录成功,你当前的信息如下')
            print(cursor.fetchone())
        else:
            print('登录失败')
    except Exception as error:
        print('捕捉到异常%s' % error)
    finally:
        cursor.close()
        conn.close()


def insert_studends():
    try:
        conn = pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
        cursor = conn.cursor()
        name = input('请输入用户名：')
        hometown = input('请输入你的家乡地址：')
        className = input('请输入你的班级')
        sql = "select * from students17 where name=%s"
        count = cursor.execute(sql, name)
        if count == 1:
            print('你输入的用户名已存在，请重新注册')
        else:
            insertSql = 'insert into students17 (name,hometown,class) values(name=%s,hometown=%s,class=%s)'
            numb = cursor.execute(insertSql, [name, hometown, className])
            conn.commit()
            print(numb)
            if numb == 1:
                print('注册成功')
                conn.close()
                cursor.close()
            else:
                print('注册失败')
    except RuntimeError as rt:
        print('捕捉到异常%s' % rt)


def select_lo():
    try:
        # 连接数据库
        conn = pymysql.connect(host="49.233.39.160", user="user", passwd="leboAa!#$123", db="lebo16", port=3306)
        # 打开游标
        # pymysql.cursors.DictCursor
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        # sql注入练习  'or 1 = 1 or'
        # name = str(input('请输入登录名'))
        #sql = "select * from students17 where   name='%s';" % (name)
        sql = "select * from students17"
        conunt = cursor.execute(sql)
        print(conunt)
        if conunt >= 1:
            print('登录成功,你当前的信息如下')
            se1=cursor.fetchone()
            print('1%s'%se1)
            # select_02 = cursor.fetchone()
            # print(select_02)
            #相对位置，要管前面取了什么数据位置
            cursor.scroll(2,mode="relative")
            se3 = cursor.fetchmany(3)
            print(se3)
            #绝对位置，不会管前面取了什么，就直接到想要的位置，2为第三条，就打印为第三条数据开始打印
            se1 = cursor.fetchone()
            print('1%s' % se1)
            cursor.scroll(2, mode="absolute")
            se3 = cursor.fetchmany(3)
            print(se3)
            se4=cursor.fetchall()
            print(se4)
        else:
            print('登录失败')
    except Exception as error:
        print('捕捉到异常%s' % error)
    finally:
        cursor.close()
        conn.close()

select_lo()
